﻿using Dapper;
using System;
using BCEntity.CRM.EnterpriseInfomation;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;

namespace BCData.CRM.EnterpriseInfomation
{
    /// <summary>
    /// 企业信息
    /// </summary>
    public class EnterpriseInfomationData : IEnterpriseInfomationData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM enterpriseinfomation";
        private const string QUERY = @"SELECT EnterpriseInfomationId,ProvinceCode,CityCode,CountyCode,CompanyType,SocialUniformCode,TaxIdNumber,RegisteredNo,OrganizationCode,
                                            PublicCompanyPhone,PublicCompanyPhoneMore,CompanyName,InsuranceSchemeNumber,IndustryCategoryId,IndustryCategoryName,PublicCompanyAddress,
                                            PublicCompanyWebsite,PublicCompanyEmail,BusinessScope,CreateTime,UpdateTime,Corporation,RegisteredCapital,RegisteredCapitalStr,
                                            RegisteredDate,ManagementForms,ProvinceName,CityName,CountyName 
                                        FROM enterpriseinfomation";
        public EnterpriseInfomationData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #region 检查企业信息是否存在
        public async Task<bool> Exists(long enterpriseInfomationId)
        {
            string sql = string.Format(@"{0} WHERE EnterpriseInfomationId=?EnterpriseInfomationId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?EnterpriseInfomationId", enterpriseInfomationId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> ExistsByCompanyName(string companyName)
        {
            string sql = string.Format(@"{0} WHERE CompanyName=?CompanyName", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?CompanyName", companyName, DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }

        public async Task<bool> ExistsBySocialUniformCode(string socialUniformCode)
        {
            string sql = string.Format(@"{0} WHERE SocialUniformCode=?SocialUniformCode", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?SocialUniformCode", socialUniformCode, DbType.AnsiString);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 导入企业信息
        public async void Import(List<EnterpriseInfomationEntity> entities)
        {
            string sql = SqlCommands.GetInsertSql("enterpriseinfomation", new string[] { "ProvinceCode", "CityCode", "CountyCode", "CompanyType", "SocialUniformCode", "TaxIdNumber",
                "RegisteredNo", "OrganizationCode", "PublicCompanyPhone", "PublicCompanyPhoneMore", "CompanyName", "InsuranceSchemeNumber", "IndustryCategoryId",
                "IndustryCategoryName", "PublicCompanyAddress", "PublicCompanyWebsite", "PublicCompanyEmail", "BusinessScope", "Corporation", "RegisteredCapital","RegisteredCapitalStr",
                "RegisteredDate", "ManagementForms", "ProvinceName", "CityName", "CountyName" });
            await this.databaseContext.Master.ExecuteAsync(sql, entities, databaseContext.Transaction);
        }
        #endregion

        #region 添加企业信息
        public async Task<EnterpriseInfomationEntity> Add(EnterpriseInfomationEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("enterpriseinfomation", new string[] { "ProvinceCode", "CityCode", "CountyCode", "CompanyType", "SocialUniformCode", "TaxIdNumber",
                "RegisteredNo", "OrganizationCode", "PublicCompanyPhone", "PublicCompanyPhoneMore", "CompanyName", "InsuranceSchemeNumber", "IndustryCategoryId",
                "IndustryCategoryName", "PublicCompanyAddress", "PublicCompanyWebsite", "PublicCompanyEmail", "BusinessScope", "Corporation", "RegisteredCapital","RegisteredCapitalStr",
                "RegisteredDate", "ManagementForms", "ProvinceName", "CityName", "CountyName" });
            sql = string.Format("{0} {1} WHERE EnterpriseInfomationId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ProvinceCode", entity.ProvinceCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CityCode", entity.CityCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CountyCode", entity.CountyCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CompanyType", entity.CompanyType?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?SocialUniformCode", entity.SocialUniformCode?.Trim(), DbType.AnsiString, size: 18);
            parameters.Add("?TaxIdNumber", entity.TaxIdNumber?.Trim(), DbType.AnsiString, size: 18);
            parameters.Add("?RegisteredNo", entity.RegisteredNo?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?OrganizationCode", entity.OrganizationCode?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?PublicCompanyPhone", entity.PublicCompanyPhone?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?PublicCompanyPhoneMore", entity.PublicCompanyPhoneMore?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?CompanyName", entity.CompanyName?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?InsuranceSchemeNumber", entity.InsuranceSchemeNumber, DbType.Int32, size: 11);
            parameters.Add("?IndustryCategoryId", entity.IndustryCategoryId, DbType.Int32, size: 11);
            parameters.Add("?IndustryCategoryName", entity.IndustryCategoryName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?PublicCompanyAddress", entity.PublicCompanyAddress?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?PublicCompanyWebsite", entity.PublicCompanyWebsite?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?PublicCompanyEmail", entity.PublicCompanyEmail?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?BusinessScope", entity.BusinessScope?.Trim(), DbType.String);
            parameters.Add("?Corporation", entity.Corporation?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?RegisteredCapital", entity.RegisteredCapital, DbType.Decimal, size: 16);
            parameters.Add("?RegisteredCapitalStr", entity.RegisteredCapitalStr, DbType.AnsiString, size: 50);
            parameters.Add("?RegisteredDate", entity.RegisteredDate, DbType.DateTime);
            parameters.Add("?ManagementForms", entity.ManagementForms?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?ProvinceName", entity.ProvinceName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?CityName", entity.CityName?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?CountyName", entity.CountyName?.Trim(), DbType.AnsiString, size: 100);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<EnterpriseInfomationEntity>(sql, parameters, databaseContext.Transaction);

        }
        #endregion

        #region 修改企业信息
        public async Task<EnterpriseInfomationEntity> Update(EnterpriseInfomationEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("enterpriseinfomation", new string[] { "ProvinceCode", "CityCode", "CountyCode", "CompanyType", "SocialUniformCode", "TaxIdNumber",
                "RegisteredNo", "OrganizationCode", "PublicCompanyPhone", "PublicCompanyPhoneMore", "CompanyName", "InsuranceSchemeNumber", "IndustryCategoryId",
                "IndustryCategoryName", "PublicCompanyAddress", "PublicCompanyWebsite", "PublicCompanyEmail", "BusinessScope", "Corporation", "RegisteredCapital","RegisteredCapitalStr",
                "RegisteredDate", "ManagementForms", "ProvinceName", "CityName", "CountyName" }, new string[] { "EnterpriseInfomationId" });
            sql = string.Format("{0} {1} WHERE EnterpriseInfomationId=?EnterpriseInfomationId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?EnterpriseInfomationId", entity.EnterpriseInfomationId, DbType.Int64, size: 20);
            parameters.Add("?ProvinceCode", entity.ProvinceCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CityCode", entity.CityCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CountyCode", entity.CountyCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CompanyType", entity.CompanyType?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?SocialUniformCode", entity.SocialUniformCode?.Trim(), DbType.AnsiString, size: 18);
            parameters.Add("?TaxIdNumber", entity.TaxIdNumber?.Trim(), DbType.AnsiString, size: 18);
            parameters.Add("?RegisteredNo", entity.RegisteredNo?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?OrganizationCode", entity.OrganizationCode?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?PublicCompanyPhone", entity.PublicCompanyPhone?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?PublicCompanyPhoneMore", entity.PublicCompanyPhoneMore?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?CompanyName", entity.CompanyName?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?InsuranceSchemeNumber", entity.InsuranceSchemeNumber, DbType.Int32, size: 11);
            parameters.Add("?IndustryCategoryId", entity.IndustryCategoryId, DbType.Int32, size: 11);
            parameters.Add("?IndustryCategoryName", entity.IndustryCategoryName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?PublicCompanyAddress", entity.PublicCompanyAddress?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?PublicCompanyWebsite", entity.PublicCompanyWebsite?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?PublicCompanyEmail", entity.PublicCompanyEmail?.Trim(), DbType.AnsiString, size: 200);
            parameters.Add("?BusinessScope", entity.BusinessScope?.Trim(), DbType.String);
            parameters.Add("?Corporation", entity.Corporation?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?RegisteredCapital", entity.RegisteredCapital, DbType.Decimal, size: 16);
            parameters.Add("?RegisteredCapitalStr", entity.RegisteredCapitalStr, DbType.AnsiString, size: 50);
            parameters.Add("?RegisteredDate", entity.RegisteredDate, DbType.DateTime);
            parameters.Add("?ManagementForms", entity.ManagementForms?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?ProvinceName", entity.ProvinceName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?CityName", entity.CityName?.Trim(), DbType.AnsiString, size: 30);
            parameters.Add("?CountyName", entity.CountyName?.Trim(), DbType.AnsiString, size: 100);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<EnterpriseInfomationEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 删除企业信息
        public async Task<bool> Delete(long enterpriseInfomationId)
        {
            string sql = @"DELETE FROM enterpriseinfomation WHERE EnterpriseInfomationId=?EnterpriseInfomationId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?EnterpriseInfomationId", enterpriseInfomationId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 获取企业信息
        public async Task<EnterpriseInfomationEntity> GetEntity(long enterpriseInfomationId)
        {
            string sql = string.Format(@"{0} WHERE EnterpriseInfomationId=?EnterpriseInfomationId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?EnterpriseInfomationId", enterpriseInfomationId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<EnterpriseInfomationEntity>(sql, parameters);
        }
        #endregion

        #region 获取企业信息列表
        public async Task<IEnumerable<EnterpriseInfomationEntity>> GetList()
        {
            return await this.databaseContext.Slave.QueryAsync<EnterpriseInfomationEntity>(QUERY);
        }
        #endregion

        #region 获取企业信息分页
        public async Task<(IEnumerable<EnterpriseInfomationEntity>, int)> Query(DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM enterpriseinfomation {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<EnterpriseInfomationEntity, int>(sql, parameters);
        }
        #endregion
    }
}



